﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using QLQTPM_ThuDuc_Group7.DTO;
using System.Windows.Forms;
using DesignLayOut_QLQTPM_Team7.DAO;

namespace QLQTPM_ThuDuc_Group7.DAO
{
    class DiaDiemDAO
    {
        public static DataTable LoadDuLieu()
        {
            string sql = "select dl.MaDuLieu, dl.MaDichVu, dv.TenDichVu, dl.MaTenDiaDiem, tdd.TenDiaDiem, dl.SoNha, dl.MaDuong, d.TenDuong, dl.MaPhuong, p.TenPhuong, dl.MaQuanHuyen, qh.TenQuanHuyen, dl.MaTinhThanh, tt.TenTinhThanh, dl.KinhDo, dl.ViDo, dl.ChuThich from DULIEU dl, TENDIADIEM tdd, PHUONG p, DUONG d, QUANHUYEN qh, TINHTHANH tt, DICHVU dv where dl.MaTenDiaDiem=tdd.MaTenDiaDiem and dl.MaDichVu=dv.MaDichVu and dl.MaDuong=d.MaDuong and dl.MaPhuong=p.MaPhuong and dl.MaQuanHuyen=qh.MaQuanHuyen and dl.MaTinhThanh=tt.MaTinhThanh";
            DataProvider dtb = new DataProvider();
            return dtb.executeQuery(sql);
        }
        //----Delete
        //public static DataTable LayMaTenDiaDiem(string tendiadiem)
        //{
        //    DataProvider dtb = new DataProvider();
        //    dtb.connect();
        //    string sql = "select * from TenDiaDiem where TenDiaDiem=" + tendiadiem;
        //    return dtb.executeQuery(sql);
        //}
        public static bool XoaDiaDiem(int matendiadiem)
        {
            bool kq = true;
            DataProvider dp = new DataProvider();
            dp.connect();
            string sql = "delete from DuLieu where MaTenDiaDiem= " + matendiadiem;
            kq = dp.executeNonQuery(sql);
            if (kq == false )
                return kq;
            return kq;
        }
        //Ham cho Search
        public DataTable LayDSDiaDiemByTuKhoaDD_DichVu(int madichvu, string tukhoa)
        {
            List<DiaDiemDTO> lstDiaDiem = new List<DiaDiemDTO>();
            DataProvider dp = new DataProvider();
            string sql = "SELECT DISTINCT DICHVU.TenDichVu, DULIEU.MaDuLieu, DULIEU.MaDichVu, DULIEU.MaTenDiaDiem, DULIEU.SoNha, DULIEU.MaDuong, DULIEU.MaPhuong, DULIEU.MaQuanHuyen, DULIEU.MaTinhThanh, DULIEU.KinhDo, DULIEU.ViDo, DULIEU.ChuThich, DUONG.TenDuong, PHUONG.TenPhuong, QUANHUYEN.TenQuanHuyen, TENDIADIEM.TenDiaDiem, TINHTHANH.TenTinhThanh "
            + "FROM (TENDIADIEM INNER JOIN (TINHTHANH INNER JOIN (QUANHUYEN INNER JOIN (PHUONG INNER JOIN (DUONG INNER JOIN (DICHVU INNER JOIN DULIEU ON DICHVU.[MaDichVu] = DULIEU.[MaDichVu]) ON DUONG.[MaDuong] = DULIEU.[MaDuong]) ON PHUONG.[MaPhuong] = DULIEU.[MaPhuong]) ON QUANHUYEN.[MaQuanHuyen] = DULIEU.[MaQuanHuyen]) ON TINHTHANH.[MaTinhThanh] = DULIEU.[MaTinhThanh]) ON TENDIADIEM.[MaTenDiaDiem] = DULIEU.[MaTenDiaDiem]) INNER JOIN TUKHOADIADIEM ON TENDIADIEM.[MaTenDiaDiem] = TUKHOADIADIEM.[MaTenDiaDiem] "
            + "WHERE TUKHOADIADIEM.[TUKHOATENDIADIEM] like '%" + tukhoa + "%'";
            if(madichvu != -1)
                sql += " AND DULIEU.MaDichVu = '" + madichvu + "'";

            DataTable dt = dp.executeQuery(sql);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DiaDiemDTO diadiem = new DiaDiemDTO();
                try
                {
                    diadiem.MaDuLieu = Convert.ToInt32(dt.Rows[i]["madulieu"]);
                    diadiem.MaDichVu = Convert.ToInt32(dt.Rows[i]["madichvu"]);
                    diadiem.MaTenDiaDiem = Convert.ToInt32(dt.Rows[i]["matendiadiem"]);
                    diadiem.TenDiaDiem = dt.Rows[i]["tendiadiem"].ToString();
                    diadiem.MaDuong = Convert.ToInt32(dt.Rows[i]["MaDuong"]);
                    diadiem.TenDuong = dt.Rows[i]["tenduong"].ToString();
                    diadiem.MaQuanHuyen = Convert.ToInt32(dt.Rows[i]["MaQuanHuyen"]);
                    diadiem.TenQuanHuyen = dt.Rows[i]["tenquanhuyen"].ToString();
                    diadiem.MaPhuong = Convert.ToInt32(dt.Rows[i]["MaPhuong"]);
                    diadiem.TenPhuong = dt.Rows[i]["tenphuong"].ToString();
                    diadiem.MaTinhThanh = Convert.ToInt32(dt.Rows[i]["MaTinhThanh"]);
                    diadiem.TenTinhThanh = dt.Rows[i]["tentinhthanh"].ToString();
                    diadiem.KinhDo = Convert.ToDouble(dt.Rows[i]["KinhDo"]);
                    diadiem.ViDo = Convert.ToDouble(dt.Rows[i]["ViDo"]);
                    diadiem.ChuThich = dt.Rows[i]["chuthich"].ToString();
                    diadiem.SoNha = dt.Rows[i]["sonha"].ToString();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    continue;
                }

                lstDiaDiem.Add(diadiem);
            }

            return dt;
        }
        // Search theo địa điểm 
        public DataTable LayDSDiaDiemByTuKhoaDD_KhuVuc(int maduong, int maphuong,int maquanhuyen,int matinhthanh, string tukhoa)
        {
            List<DiaDiemDTO> lstDiaDiem = new List<DiaDiemDTO>();
            DataProvider dp = new DataProvider();
            string sql = "SELECT DISTINCT DICHVU.TenDichVu, DULIEU.MaDuLieu, DULIEU.MaDichVu, DULIEU.MaTenDiaDiem, DULIEU.SoNha, DULIEU.MaDuong, DULIEU.MaPhuong, DULIEU.MaQuanHuyen, DULIEU.MaTinhThanh, DULIEU.KinhDo, DULIEU.ViDo, DULIEU.ChuThich, DUONG.TenDuong, PHUONG.TenPhuong, QUANHUYEN.TenQuanHuyen, TENDIADIEM.TenDiaDiem, TINHTHANH.TenTinhThanh "
            + "FROM (TENDIADIEM INNER JOIN (TINHTHANH INNER JOIN (QUANHUYEN INNER JOIN (PHUONG INNER JOIN (DUONG INNER JOIN (DICHVU INNER JOIN DULIEU ON DICHVU.[MaDichVu] = DULIEU.[MaDichVu]) ON DUONG.[MaDuong] = DULIEU.[MaDuong]) ON PHUONG.[MaPhuong] = DULIEU.[MaPhuong]) ON QUANHUYEN.[MaQuanHuyen] = DULIEU.[MaQuanHuyen]) ON TINHTHANH.[MaTinhThanh] = DULIEU.[MaTinhThanh]) ON TENDIADIEM.[MaTenDiaDiem] = DULIEU.[MaTenDiaDiem]) INNER JOIN TUKHOADIADIEM ON TENDIADIEM.[MaTenDiaDiem] = TUKHOADIADIEM.[MaTenDiaDiem] "
            + "WHERE TUKHOADIADIEM.[TUKHOATENDIADIEM] like '%" + tukhoa + "%'";
            if(maphuong != -1)
                sql += " AND DULIEU.Maphuong = '" + maphuong + "'";
            if(maquanhuyen != -1)
                sql += " AND DULIEU.Maquanhuyen = '" + maquanhuyen + "'";
            if(matinhthanh != -1)
                sql += " AND DULIEU.Matinhthanh = '" + matinhthanh + "'";
            if(maduong != -1)
                sql += " AND DULIEU.MaDuong = '" + maduong + "'";

            DataTable dt = dp.executeQuery(sql);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DiaDiemDTO diadiem = new DiaDiemDTO();
                try
                {
                    diadiem.MaDuLieu = Convert.ToInt32(dt.Rows[i]["madulieu"]);
                    diadiem.MaDichVu = Convert.ToInt32(dt.Rows[i]["madichvu"]);
                    diadiem.MaTenDiaDiem = Convert.ToInt32(dt.Rows[i]["matendiadiem"]);
                    diadiem.TenDiaDiem = dt.Rows[i]["tendiadiem"].ToString();
                    diadiem.MaDuong = Convert.ToInt32(dt.Rows[i]["MaDuong"]);
                    diadiem.TenDuong = dt.Rows[i]["tenduong"].ToString();
                    diadiem.MaQuanHuyen = Convert.ToInt32(dt.Rows[i]["MaQuanHuyen"]);
                    diadiem.TenQuanHuyen = dt.Rows[i]["tenquanhuyen"].ToString();
                    diadiem.MaPhuong = Convert.ToInt32(dt.Rows[i]["MaPhuong"]);
                    diadiem.TenPhuong = dt.Rows[i]["tenphuong"].ToString();
                    diadiem.MaTinhThanh = Convert.ToInt32(dt.Rows[i]["MaTinhThanh"]);
                    diadiem.TenTinhThanh = dt.Rows[i]["tentinhthanh"].ToString();
                    diadiem.KinhDo = Convert.ToDouble(dt.Rows[i]["KinhDo"]);
                    diadiem.ViDo = Convert.ToDouble(dt.Rows[i]["ViDo"]);
                    diadiem.ChuThich = dt.Rows[i]["chuthich"].ToString();
                    diadiem.SoNha = dt.Rows[i]["sonha"].ToString();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    continue;
                }

                lstDiaDiem.Add(diadiem);
            }

            return dt;
        }
        //Ham cho Search
        public int ThemDanhSachDuLieu(List<DiaDiemDTO> lstDuLieu)
        {
            int dem = 0;//Đếm số lượng insert thành công

            DataProvider dp = new DataProvider();
            dp.connect();
            for (int i = 0; i < lstDuLieu.Count; i++)
            {
                DiaDiemDTO duLieu = lstDuLieu.ElementAt(i);

                string sql = "Insert into dulieu(madichvu, matendiadiem, sonha, maphuong, maQuanHuyen, matinhthanh, kinhdo, vido, chuthich)"
                    + "values (" + duLieu.MaDichVu + ", " + duLieu.MaTenDiaDiem + ", '" + duLieu.SoNha + "', " + duLieu.MaPhuong + ", " + duLieu.MaQuanHuyen + ", "
                    + "" + duLieu.MaTinhThanh + ", " + duLieu.KinhDo + ", " + duLieu.ViDo + ", '" + duLieu.ChuThich + "')";
                
                bool kq = false;
                try
                {
                    kq = dp.executeNonQuery(sql);
                }
                catch (Exception oleEX)
                {
                    //Log4Net
                    //Console.WriteLine("Dong thu " + temp + " bi loi:" + oleEX.Message);
                    Console.WriteLine(oleEX.Message);
                }
                if (kq)
                    dem++;
            }
            dp.disconnect();

            return dem;
        }
        //-----Bat dau insert----------
        DataProvider dataProvider = new DataProvider();

        //Hàm thêm địa điểm
        public bool ThemDiaDiem(DiaDiemDTO diaDiemDTO)
        {
            bool flag = false;
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            dataProvider.connect();
            try
            {
                // B3: Tao chuoi strSQL thao tac CSDL
                int maTenDiaDiem = MaTenDiaDiem(diaDiemDTO.TenDiaDiem);
                int maDuong = MaDuong(diaDiemDTO.TenDuong);
                int maPhuong = MaPhuong(diaDiemDTO.TenPhuong);
                int maQuanHuyen = MaQuanHuyen(diaDiemDTO.TenQuanHuyen);
                int maTinhThanh = 16;
                string strSQL = "insert into dulieu (MaDichVu,MaTenDiaDiem,SoNha,MaDuong,MaPhuong,MaQuanHuyen,MaTinhThanh,KinhDo,ViDo,ChuThich) values (" + diaDiemDTO.MaDichVu + "," + maTenDiaDiem + "," + diaDiemDTO.SoNha + "," + maDuong + "," + maPhuong + "," + maQuanHuyen + "," + maTinhThanh + "," + diaDiemDTO.KinhDo + "," + diaDiemDTO.ViDo + ",'" + diaDiemDTO.ChuThich + "')";
                bool co = dataProvider.executeNonQuery(strSQL);
                if (co == true)
                {
                    flag = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi insert" + ex.Message);
            }
            return flag;
        }

        //Lấy mã đường ứng với tên đường nhâp ở textbox
        public int MaDuong(string tenDuong)
        {
            OleDbConnection cn;
            int ma = -1;
            try
            {
                cn = DataProvider.ConnectionData();
                // B3: Tao chuoi strSQL thao tac CSDL
                string strSQL = "select * from duong where TenDuong = '" + tenDuong + "'";
                object kt = dataProvider.executeScalar(strSQL);
                if (kt != null)
                {
                    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                    ma = (int)cmd.ExecuteScalar();
                }
                else
                {
                    strSQL = "insert into duong(TenDuong) values ('" + tenDuong + "')";
                    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                    dataProvider.executeNonQuery(strSQL);

                    strSQL = "select * from duong where TenDuong = '" + tenDuong + "'";
                    cmd = new OleDbCommand(strSQL, cn);
                    ma = (int)cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi" + ex.Message);
            }
            return ma;
        }

        //Lấy mã phường ứng với tên phường nhâp ở textbox
        public int MaPhuong(string tenPhuong)
        {
            OleDbConnection cn;
            int ma = -1;
            try
            {
                cn = DataProvider.ConnectionData();
                // B3: Tao chuoi strSQL thao tac CSDL
                string strSQL = "select * from phuong where TenPhuong = '" + tenPhuong + "'";
                object kt = dataProvider.executeScalar(strSQL);
                if (kt != null)
                {
                    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                    ma = (int)cmd.ExecuteScalar();
                }
                else
                {
                    strSQL = "insert into phuong(TenPhuong) values ('" + tenPhuong + "')";
                    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                    dataProvider.executeNonQuery(strSQL);

                    strSQL = "select * from phuong where TenPhuong = '" + tenPhuong + "'";
                    cmd = new OleDbCommand(strSQL, cn);
                    ma = (int)cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi" + ex.Message);
            }
            return ma;
        }

        //Lấy mã quận huyện ứng với tên quận huyện nhâp ở textbox
        public int MaQuanHuyen(string tenQuanHuyen)
        {
            OleDbConnection cn;
            int ma = -1;
            try
            {
                cn = DataProvider.ConnectionData();
                // B3: Tao chuoi strSQL thao tac CSDL
                string strSQL = "select * from quanhuyen where TenQuanHuyen = '" + tenQuanHuyen + "'";
                object kt = dataProvider.executeScalar(strSQL);
                if (kt != null)
                {
                    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                    ma = (int)cmd.ExecuteScalar();
                }
                else
                {
                    strSQL = "insert into quanhuyen(TenQuanHuyen) values ('" + tenQuanHuyen + "')";
                    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                    dataProvider.executeNonQuery(strSQL);

                    strSQL = "select * from quanhuyen where TenPhuong = '" + tenQuanHuyen + "'";
                    cmd = new OleDbCommand(strSQL, cn);
                    ma = (int)cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi" + ex.Message);
            }
            return ma;
        }

        //Lấy mã tên địa điểm ứng với địa điểm nhâp ở textbox
        public int MaTenDiaDiem(string tenDiaDiem)
        {
            OleDbConnection cn;
            int ma = -1;
            try
            {
                cn = DataProvider.ConnectionData();
                // B3: Tao chuoi strSQL thao tac CSDL
                string strSQL = "insert into tendiadiem(TenDiaDiem) values ('" + tenDiaDiem + "')";
                OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                dataProvider.executeNonQuery(strSQL);

                strSQL = "select * from tendiadiem where TenDiaDiem = '" + tenDiaDiem + "'";
                cmd = new OleDbCommand(strSQL, cn);
                ma = (int)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi" + ex.Message);
            }
            return ma;
        }

        public bool isNumberic(string str)
        {
            try
            {
                str = str.Trim();
                double foo = double.Parse(str);
                return (true);
            }
            catch (FormatException)
            {
                return (false);
            }
        }

        //Lấy Dữ liệu Địa Điểm theo mã tên địa điểm --Delete---
        public static DataTable LayDuLieuDDTheoMaTenDiaDiem(int matendiadiem)
        {
            DataTable tb = null;
            DataProvider dp = new DataProvider();
            dp.connect();
            string sql = "SELECT TENDIADIEM.MaTenDiaDiem, TENDIADIEM.TenDiaDiem, DULIEU.SoNha, DUONG.TenDuong, PHUONG.TenPhuong, QUANHUYEN.TenQuanHuyen, TINHTHANH.TenTinhThanh" +
                "FROM TINHTHANH INNER JOIN (TENDIADIEM INNER JOIN (QUANHUYEN INNER JOIN (PHUONG INNER JOIN (DUONG INNER JOIN DULIEU ON DUONG.MaDuong = DULIEU.MaDuong) ON PHUONG.MaPhuong = DULIEU.MaPhuong) ON QUANHUYEN.MaQuanHuyen = DULIEU.MaQuanHuyen) ON TENDIADIEM.MaTenDiaDiem = DULIEU.MaTenDiaDiem) ON TINHTHANH.MaTinhThanh = DULIEU.MaTinhThanh" +
                "WHERE (((TENDIADIEM.MaTenDiaDiem)=" + matendiadiem + "))";
            tb = dp.executeQuery(sql);
            return tb;
        }
        //---Ket thuc insert-----

        //-----Bat dau Update------
        public static DiaDiemDTO LoadThongTinDiaDiem(int ma)
        {
            string sql = "select * from DULIEU where MATENDIADIEM=" + ma;
            DataProvider dtb = new DataProvider();
            DataTable tb = dtb.executeQuery(sql);

            string sonha = tb.Rows[0]["SONHA"].ToString();
            int maduong = Convert.ToInt32(tb.Rows[0]["MADUONG"].ToString());
            int maphuong = Convert.ToInt32(tb.Rows[0]["MAPHUONG"].ToString());
            int maquanhuyen = Convert.ToInt32(tb.Rows[0]["MAQUANHUYEN"].ToString());
            int matinhthanh = Convert.ToInt32(tb.Rows[0]["MATINHTHANH"].ToString());
            int madichvu = Convert.ToInt32(tb.Rows[0]["MADICHVU"].ToString());
            double kinhdo = double.Parse(tb.Rows[0]["KINHDO"].ToString());
            double vido = double.Parse(tb.Rows[0]["VIDO"].ToString());
            string chuthich = tb.Rows[0]["CHUTHICH"].ToString();
            DiaDiemDTO dd = new DiaDiemDTO(ma, madichvu, sonha, maduong, maphuong, maquanhuyen, matinhthanh, kinhdo, vido, chuthich);
            return dd;
        }
        public static DataTable LoadTenDiaDiem()
        {
            string sql = "select * from TENDIADIEM";
            DataProvider dtb = new DataProvider();
            return dtb.executeQuery(sql);
        }

        public static bool SuaDiaDiem(DiaDiemDTO dd)
        {
            DataProvider dtb = new DataProvider();
            dtb.connect();
            string sql = "";
            sql = "update DULIEU set ";
            sql += "SoNha ='" + dd.SoNha + "',";
            sql += "MaDuong =" + dd.MaDuong + ",";
            sql += "MaPhuong =" + dd.MaPhuong + ",";
            sql += "MaQuanHuyen =" + dd.MaQuanHuyen + ",";
            sql += "MaTinhThanh =" + dd.MaTinhThanh + ",";
            sql += "MaDichVu =" + dd.MaDichVu + ",";
            sql += "KinhDo =" + dd.KinhDo + ",";
            sql += "ViDo =" + dd.ViDo + ",";
            sql += "ChuThich ='" + dd.ChuThich + "'";
            sql += " where MaTenDiaDiem =" + dd.MaTenDiaDiem;

            return dtb.executeNonQuery(sql);
        }
        //-----Ket thuc Updata------
    }
}
